﻿using System;
using System.Collections.Generic;
using Shuba.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Shuba.Data
{
    public class Pipeline
    {
        public Pipeline()
        {
        }

        public int GetRowNumberList(string value)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM ( 
                    SELECT [ROW_ID],[UNIT_CODE],[FULL_NAME],[JENIS_USAHA],[RENCANA_PLAFON] 
                      ,[MOBILE_PHONE],[OFFICE_PHONE],[RESIDENTIAL_PHONE],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS] 
                      ,[KELURAHAN_BUSINESS],[ADDRESS_BUSINESS],[ZIP_CODE_BUSINESS],[DATE_VISIT_PLAN],[PROSPECT_TYPE] 
                      ,[DEBTOR_TYPE],[CHANNELING],[OMZET_1],[OMZET_2],[OMZET_3] 
                      ,[OMZET_4],[OMZET_5],[OMZET_6],[OMZET_7],[OMZET_8] 
                      ,[OMZET_9],[OMZET_10],[OMZET_11],[OMZET_12],[OMZET_AVERAGE] 
                      ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_DOWNLOADED] 
                      ,[DOWNLOAD_BY],[DOWNLOAD_DATE],[EKSTERNAL],[REFERENSI],[IS_ASSIGNED],[ASSIGNED_TO] 
                    FROM TBL_PIPELINE (NOLOCK) WHERE (UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%')
                    )AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<PipelineInfo> GetList(string value, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<PipelineInfo> list = new List<PipelineInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT  * FROM ( 
                  SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, 
                    [ROW_ID],[UNIT_CODE],[FULL_NAME],[JENIS_USAHA],[RENCANA_PLAFON] 
                      ,[MOBILE_PHONE],[OFFICE_PHONE],[RESIDENTIAL_PHONE],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS] 
                      ,[KELURAHAN_BUSINESS],[ADDRESS_BUSINESS],[ZIP_CODE_BUSINESS],[DATE_VISIT_PLAN],[PROSPECT_TYPE] 
                      ,[DEBTOR_TYPE],[CHANNELING],[OMZET_1],[OMZET_2],[OMZET_3] 
                      ,[OMZET_4],[OMZET_5],[OMZET_6],[OMZET_7],[OMZET_8] 
                      ,[OMZET_9],[OMZET_10],[OMZET_11],[OMZET_12],[OMZET_AVERAGE] 
                      ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_DOWNLOADED] 
                      ,[DOWNLOAD_BY],[DOWNLOAD_DATE],[EKSTERNAL],[REFERENSI],[IS_ASSIGNED],[ASSIGNED_TO] 
                    FROM TBL_PIPELINE (NOLOCK) WHERE (UNIT_CODE LIKE @PARAM) OR (FULL_NAME LIKE @PARAM)
                  ) AS LogWithRowNumbers 
                  WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + value + "%");
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineInfo(dr.GetGuid(1).ToString(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5),
                        dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10),
                        dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetDateTime(14), dr.GetString(15),
                        dr.GetString(16), dr.GetString(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20),
                        dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25),
                        dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetDouble(30),
                        dr.GetString(31), dr.GetDateTime(32), dr.GetString(33), dr.GetDateTime(34),
                        CharToBool(dr.GetString(35)), dr.GetString(36), dr.GetDateTime(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetString(41)));
                }
                dr.Close();
            }
            return list;
        }

        public List<PipelineInfo> GetList(string value)
        {
            List<PipelineInfo> list = new List<PipelineInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;

            sql = @"SELECT [ROW_ID],[UNIT_CODE],[FULL_NAME],[JENIS_USAHA],[RENCANA_PLAFON] 
                      ,[MOBILE_PHONE],[OFFICE_PHONE],[RESIDENTIAL_PHONE],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS] 
                      ,[KELURAHAN_BUSINESS],[ADDRESS_BUSINESS],[ZIP_CODE_BUSINESS],[DATE_VISIT_PLAN],[PROSPECT_TYPE] 
                      ,[DEBTOR_TYPE],[CHANNELING],[OMZET_1],[OMZET_2],[OMZET_3] 
                      ,[OMZET_4],[OMZET_5],[OMZET_6],[OMZET_7],[OMZET_8] 
                      ,[OMZET_9],[OMZET_10],[OMZET_11],[OMZET_12],[OMZET_AVERAGE] 
                      ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_DOWNLOADED] 
                      ,[DOWNLOAD_BY],[DOWNLOAD_DATE],[EKSTERNAL],[REFERENSI],[IS_ASSIGNED],[ASSIGNED_TO] 
                    FROM TBL_PIPELINE WHERE (UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%') ORDER BY UPDATE_DATE DESC";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineInfo(dr.GetGuid(0).ToString(), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4),
                        dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9),
                        dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14),
                        dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19),
                        dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24),
                        dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29),
                        dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33),
                        CharToBool(dr.GetString(34)), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetString(40)));
                }
                dr.Close();
            }
            return list;
        }

        public int GetRowNumberPipelineApproval(string update_by)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM ( 
                    SELECT * FROM TBL_PIPELINE_TMP (NOLOCK) WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1')AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            //LTRIM(RTRIM(BATCH_ID))='' AND 
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<PipelineInfo> GetListApproval(string UPDATE_BY, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<PipelineInfo> list = new List<PipelineInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT  * FROM ( 
                            SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, * 
                            FROM TBL_PIPELINE_TMP (NOLOCK)
                            WHERE (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1')
                         )AS LogWithRowNumbers 
                         WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UPDATE_BY);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4),
                        dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9),
                        dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14),
                        dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19),
                        dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24),
                        dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29),
                        dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33),
                        CharToBool(dr.GetString(34)), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39)));
                }
                dr.Close();
            }
            return list;
        }

        public List<PipelineInfo> GetListApproval(string value, string UPDATE_BY)
        {
            List<PipelineInfo> list = new List<PipelineInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_PIPELINE_TMP WHERE ((UNIT_CODE LIKE '%' + @PARAM + '%') OR (FULL_NAME LIKE '%' + @PARAM + '%')) AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UPDATE_BY);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDouble(3),
                        dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8),
                        dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13),
                        dr.GetString(14), dr.GetString(15), dr.GetDouble(16), dr.GetDouble(17), dr.GetDouble(18),
                        dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23),
                        dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28),
                        dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetDateTime(32),
                        CharToBool(dr.GetString(33)), dr.GetString(34), dr.GetDateTime(35), dr.GetString(36), dr.GetString(37), dr.GetString(38)));
                }
                dr.Close();
            }
            return list;
        }

        public PipelineInfo Get(string ROW_ID)
        {
            PipelineInfo ci = new PipelineInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT [ROW_ID],[UNIT_CODE],[FULL_NAME],[JENIS_USAHA],[RENCANA_PLAFON] 
                                  ,[MOBILE_PHONE],[OFFICE_PHONE],[RESIDENTIAL_PHONE],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS] 
                                  ,[KELURAHAN_BUSINESS],[ADDRESS_BUSINESS],[ZIP_CODE_BUSINESS],[DATE_VISIT_PLAN],[PROSPECT_TYPE] 
                                  ,[DEBTOR_TYPE],[CHANNELING],[OMZET_1],[OMZET_2],[OMZET_3] 
                                  ,[OMZET_4],[OMZET_5],[OMZET_6],[OMZET_7],[OMZET_8] 
                                  ,[OMZET_9],[OMZET_10],[OMZET_11],[OMZET_12],[OMZET_AVERAGE] 
                                  ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_DOWNLOADED] 
                                  ,[DOWNLOAD_BY],[DOWNLOAD_DATE],[EKSTERNAL],[REFERENSI],[IS_ASSIGNED],[ASSIGNED_TO] 
                                 FROM TBL_PIPELINE WHERE LTRIM(RTRIM(ROW_ID)) LIKE '' + @ROW_ID + '' ");
            db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID.Trim());
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new PipelineInfo(dr.GetGuid(0).ToString(), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4),
                        dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9),
                        dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14),
                        dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19),
                        dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24),
                        dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29),
                        dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33),
                        CharToBool(dr.GetString(34)), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetString(40));
                }
                dr.Close();
            }
            return ci;
        }

        public bool IsExist(string UNIT_CODE, string FULL_NAME, string KODYA_KAB_BUSINESS, string ADDRESS_BUSINESS)
        {
            bool result = false;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 UNIT_CODE FROM TBL_PIPELINE NOLOCK
                    WHERE LTRIM(RTRIM(UNIT_CODE)) = @UNIT_CODE
                    AND LTRIM(RTRIM(FULL_NAME)) = @FULL_NAME
                    AND LTRIM(RTRIM(KODYA_KAB_BUSINESS)) = @KODYA_KAB_BUSINESS
                    AND LTRIM(RTRIM(ADDRESS_BUSINESS)) = @ADDRESS_BUSINESS
                    UNION
                    SELECT TOP 1 UNIT_CODE FROM TBL_PIPELINE_TMP NOLOCK
                    WHERE LTRIM(RTRIM(UNIT_CODE)) = @UNIT_CODE
                    AND LTRIM(RTRIM(FULL_NAME)) = @FULL_NAME
                    AND LTRIM(RTRIM(KODYA_KAB_BUSINESS)) = @KODYA_KAB_BUSINESS
                    AND LTRIM(RTRIM(ADDRESS_BUSINESS)) = @ADDRESS_BUSINESS");
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE == null ? "" : UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME == null ? "" : FULL_NAME.Trim());
            db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, KODYA_KAB_BUSINESS == null ? "" : KODYA_KAB_BUSINESS.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS == null ? "" : ADDRESS_BUSINESS.Trim());
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    result = true;
                }
                dr.Close();
            }
            return result;
        }

        public PipelineInfo GetApprovalByID(string UNIT_CODE, string FULL_NAME, string ADDRESS_BUSINESS)
        {
            PipelineInfo ci = new PipelineInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_PIPELINE_TMP WHERE LTRIM(RTRIM(UNIT_CODE)) LIKE '' + @UNIT_CODE + '' 
                                AND LTRIM(RTRIM(FULL_NAME)) LIKE '' + @FULL_NAME + ''
                                AND LTRIM(RTRIM(ADDRESS_BUSINESS)) LIKE '' + @ADDRESS_BUSINESS + '' 
                                AND IS_MAIN_DELETED = '1'");
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new PipelineInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDouble(3),
                        dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8),
                        dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13),
                        dr.GetString(14), dr.GetString(15), dr.GetDouble(16), dr.GetDouble(17), dr.GetDouble(18),
                        dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23),
                        dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28),
                        dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetDateTime(32),
                        CharToBool(dr.GetString(33)), dr.GetString(34), dr.GetDateTime(35), dr.GetString(36), dr.GetString(37), dr.GetString(38));
                }
                dr.Close();
            }
            return ci;
        }

        public int Save(PipelineInfo PIPELINE_TMP)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PIPELINE_TMP SET 
                                    UNIT_CODE = LEFT(@UNIT_CODE,6), FULL_NAME = LEFT(@FULL_NAME,50),
                                    JENIS_USAHA = LEFT(@JENIS_USAHA,100), RENCANA_PLAFON = @RENCANA_PLAFON, 
                                    MOBILE_PHONE = LEFT(@MOBILE_PHONE,18), OFFICE_PHONE = RIGHT(@OFFICE_PHONE,12), 
                                    RESIDENTIAL_PHONE = RIGHT(@RESIDENTIAL_PHONE,12), KODYA_KAB_BUSINESS = LEFT(@KODYA_KAB_BUSINESS,50),
                                    KECAMATAN_BUSINESS = LEFT(@KECAMATAN_BUSINESS,50), KELURAHAN_BUSINESS = LEFT(@KELURAHAN_BUSINESS,50),
                                    ADDRESS_BUSINESS = LEFT(@ADDRESS_BUSINESS,255), ZIP_CODE_BUSINESS = LEFT(@ZIP_CODE_BUSINESS,6),
                                    PROSPECT_TYPE = LEFT(@PROSPECT_TYPE,10), DEBTOR_TYPE = LEFT(@DEBTOR_TYPE,10),
                                    CHANNELING = LEFT(@CHANNELING,10), OMZET_1 = @OMZET_1, OMZET_2 = @OMZET_2, OMZET_3 = @OMZET_3,
                                    OMZET_4 = @OMZET_4, OMZET_5 = @OMZET_5, OMZET_6 = @OMZET_6, OMZET_7 = @OMZET_7, OMZET_8 = @OMZET_8,
                                    OMZET_9 = @OMZET_9, OMZET_10 = @OMZET_10, OMZET_11 = @OMZET_11, OMZET_12 = @OMZET_12, 
                                    OMZET_AVERAGE = @OMZET_AVERAGE, ENTRY_BY = @ENTRY_BY, 
                                    ENTRY_DATE = @ENTRY_DATE, UPDATE_BY = @UPDATE_BY,
                                    UPDATE_DATE = @UPDATE_DATE, ACT_TYPE = @ACT_TYPE, [EKSTERNAL] = @EKSTERNAL, REFERENSI = @REFERENSI, IS_MAIN_DELETED = '1'
                                WHERE (LTRIM(RTRIM(UNIT_CODE)) = LTRIM(RTRIM(@UNIT_CODE))) AND (LTRIM(RTRIM(FULL_NAME))=LTRIM(RTRIM(@FULL_NAME)))
                                AND (LTRIM(RTRIM(KODYA_KAB_BUSINESS)) = LTRIM(RTRIM(@KODYA_KAB_BUSINESS))) AND (LTRIM(RTRIM(ADDRESS_BUSINESS)) = LTRIM(RTRIM(@ADDRESS_BUSINESS)));
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PIPELINE_TMP
                                (
                                    UNIT_CODE, FULL_NAME, JENIS_USAHA, RENCANA_PLAFON,
                                    MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE, KODYA_KAB_BUSINESS,
                                    KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, ADDRESS_BUSINESS, 
                                    ZIP_CODE_BUSINESS, DATE_VISIT_PLAN, PROSPECT_TYPE, DEBTOR_TYPE, CHANNELING,
                                    OMZET_1, OMZET_2, OMZET_3, OMZET_4, OMZET_5, OMZET_6, OMZET_7, OMZET_8, 
                                    OMZET_9, OMZET_10, OMZET_11, OMZET_12, OMZET_AVERAGE, 
                                    ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE, [EKSTERNAL], REFERENSI, IS_MAIN_DELETED
                                ) 
                                VALUES
                                (
                                    LEFT(@UNIT_CODE,6), LEFT(@FULL_NAME,50),
                                    LEFT(@JENIS_USAHA,100), @RENCANA_PLAFON, LEFT(@MOBILE_PHONE,18),
                                    RIGHT(@OFFICE_PHONE,12), RIGHT(@RESIDENTIAL_PHONE,12),
                                    LEFT(@KODYA_KAB_BUSINESS,50), LEFT(@KECAMATAN_BUSINESS,50), LEFT(@KELURAHAN_BUSINESS,50),
                                    LEFT(@ADDRESS_BUSINESS,255), LEFT(@ZIP_CODE_BUSINESS,6),
                                    @DATE_VISIT_PLAN, LEFT(@PROSPECT_TYPE,10), LEFT(@DEBTOR_TYPE,10), LEFT(@CHANNELING,10), 
                                    @OMZET_1, @OMZET_2, @OMZET_3, @OMZET_4, @OMZET_5, @OMZET_6, @OMZET_7, @OMZET_8, 
                                    @OMZET_9, @OMZET_10, @OMZET_11, @OMZET_12, @OMZET_AVERAGE, 
                                    @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE, @EKSTERNAL, @REFERENSI, '1'
                                );
                            ");
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, PIPELINE_TMP.UNIT_CODE);
                db.AddInParameter(cmd, "@FULL_NAME", DbType.String, PIPELINE_TMP.FULL_NAME);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, PIPELINE_TMP.JENIS_USAHA);
                db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, PIPELINE_TMP.RENCANA_PLAFON);
                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, PIPELINE_TMP.MOBILE_PHONE);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, PIPELINE_TMP.OFFICE_PHONE);
                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, PIPELINE_TMP.RESIDENTIAL_PHONE);
                db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, PIPELINE_TMP.KODYA_KAB_BUSINESS);
                db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, PIPELINE_TMP.KECAMATAN_BUSINESS);
                db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, PIPELINE_TMP.KELURAHAN_BUSINESS);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, PIPELINE_TMP.ADDRESS_BUSINESS);
                db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, PIPELINE_TMP.ZIP_CODE_BUSINESS);
                db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, PIPELINE_TMP.DATE_VISIT_PLAN);
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, PIPELINE_TMP.PROSPECT_TYPE);
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, PIPELINE_TMP.DEBTOR_TYPE);
                db.AddInParameter(cmd, "@CHANNELING", DbType.String, PIPELINE_TMP.CHANNELING);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, PIPELINE_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, PIPELINE_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, PIPELINE_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, PIPELINE_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, PIPELINE_TMP.ACT_TYPE);
                db.AddInParameter(cmd, "@OMZET_1", DbType.String, PIPELINE_TMP.OMZET_1);
                db.AddInParameter(cmd, "@OMZET_2", DbType.String, PIPELINE_TMP.OMZET_2);
                db.AddInParameter(cmd, "@OMZET_3", DbType.String, PIPELINE_TMP.OMZET_3);
                db.AddInParameter(cmd, "@OMZET_4", DbType.String, PIPELINE_TMP.OMZET_4);
                db.AddInParameter(cmd, "@OMZET_5", DbType.String, PIPELINE_TMP.OMZET_5);
                db.AddInParameter(cmd, "@OMZET_6", DbType.String, PIPELINE_TMP.OMZET_6);
                db.AddInParameter(cmd, "@OMZET_7", DbType.String, PIPELINE_TMP.OMZET_7);
                db.AddInParameter(cmd, "@OMZET_8", DbType.String, PIPELINE_TMP.OMZET_8);
                db.AddInParameter(cmd, "@OMZET_9", DbType.String, PIPELINE_TMP.OMZET_9);
                db.AddInParameter(cmd, "@OMZET_10", DbType.String, PIPELINE_TMP.OMZET_10);
                db.AddInParameter(cmd, "@OMZET_11", DbType.String, PIPELINE_TMP.OMZET_11);
                db.AddInParameter(cmd, "@OMZET_12", DbType.String, PIPELINE_TMP.OMZET_12);
                db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.String, PIPELINE_TMP.OMZET_AVERAGE);
                db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, PIPELINE_TMP.EKSTERNAL);
                db.AddInParameter(cmd, "@REFERENSI", DbType.String, PIPELINE_TMP.REFERENSI);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }

        public int Approve(string UNIT_CODE, string FULL_NAME, string ADDRESS_BUSINESS, string ACT_TYPE)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("PIPELINE_APPROVAL");

            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ACT_TYPE.Trim());

            return db.ExecuteNonQuery(cmd);
        }

        public int ApproveBulk(string UNIT_CODE, string FULL_NAME, string KODYA_KAB_BUSINESS, string ADDRESS_BUSINESS, string ACT_TYPE)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("PIPELINE_APPROVAL_BULK");

            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, KODYA_KAB_BUSINESS.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ACT_TYPE.Trim());

            return db.ExecuteNonQuery(cmd);
        }


        public int Reject(string UNIT_CODE, string FULL_NAME, string ADDRESS_BUSINESS)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                            DELETE FROM TBL_PIPELINE_TMP
                            WHERE (LTRIM(RTRIM(UNIT_CODE)) = @UNIT_CODE) 
                                  AND (LTRIM(RTRIM(FULL_NAME)) = @FULL_NAME)
                                  AND (LTRIM(RTRIM(ADDRESS_BUSINESS)) = @ADDRESS_BUSINESS) 
                                  AND IS_MAIN_DELETED = '1'");
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, UNIT_CODE.Trim());
            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, FULL_NAME.Trim());
            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, ADDRESS_BUSINESS.Trim());
            return db.ExecuteNonQuery(cmd);
        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        private string BoolToChar(bool source)
        {
            if (source)
            {
                return "1";
            }
            else
            {
                return "0";
            }
        }

        public List<PipelineInfo> GetListAssignSO(string NIP, string VALUE)
        {
            List<PipelineInfo> list = new List<PipelineInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 

            SELECT [ROW_ID],[UNIT_CODE],[FULL_NAME],[JENIS_USAHA]
                  ,ISNULL([RENCANA_PLAFON], 0) AS RENCANA_PLAFON
                  ,[MOBILE_PHONE],[OFFICE_PHONE],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS] 
                  ,[KELURAHAN_BUSINESS],[ADDRESS_BUSINESS],[ZIP_CODE_BUSINESS]
                  ,ISNULL(MST_PROSPECT_TYPE.MS_STD_NAME, '') AS PROSPECT_TYPE
                  ,ISNULL(MST_DEBTOR_TYPE.MS_STD_NAME, '') AS DEBTOR_TYPE
                  ,ISNULL(MST_CHANNEL.CHANNEL_NAME, '') AS CHANNELING
                  ,ISNULL(MST_EXTERNAL.MS_STD_NAME, '') AS [EKSTERNAL]
               FROM TBL_PIPELINE 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS MST_PROSPECT_TYPE ON TBL_PIPELINE.PROSPECT_TYPE = MST_PROSPECT_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS MST_DEBTOR_TYPE ON TBL_PIPELINE.DEBTOR_TYPE = MST_DEBTOR_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS MST_CHANNEL ON TBL_PIPELINE.CHANNELING = MST_CHANNEL.CHANNEL_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 15) AS MST_EXTERNAL ON TBL_PIPELINE.[EKSTERNAL] = MST_EXTERNAL.MS_STD_ID
            WHERE  IS_ASSIGNED = '0' 
                   AND (FULL_NAME LIKE @PARAM) 
                   AND UNIT_CODE IN 
                        (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID)
                    ORDER BY UNIT_CODE, UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + VALUE + "%");
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineInfo(dr.GetGuid(0).ToString(), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4),
                        dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9),
                        dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetString(14),
                        dr.GetString(15)));
                }
                dr.Close();
            }
            return list;
        }

        public List<PipelineInfo> GetListAssignSOPaging(string NIP, string VALUE, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<PipelineInfo> list = new List<PipelineInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"
            DECLARE @PROCESS_DATE DATETIME
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS

            SELECT * FROM ( 
                SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, 
                [ROW_ID],[UNIT_CODE],[FULL_NAME],[JENIS_USAHA]
                  ,ISNULL([RENCANA_PLAFON], 0) AS RENCANA_PLAFON
                  ,[MOBILE_PHONE],[OFFICE_PHONE],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS] 
                  ,[KELURAHAN_BUSINESS],[ADDRESS_BUSINESS],[ZIP_CODE_BUSINESS]
                  ,ISNULL(MST_PROSPECT_TYPE.MS_STD_NAME, '') AS PROSPECT_TYPE
                  ,ISNULL(MST_DEBTOR_TYPE.MS_STD_NAME, '') AS DEBTOR_TYPE
                  ,ISNULL(MST_CHANNEL.CHANNEL_NAME, '') AS CHANNELING
                  ,ISNULL(MST_EXTERNAL.MS_STD_NAME, '') AS [EKSTERNAL]
               FROM TBL_PIPELINE 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS MST_PROSPECT_TYPE ON TBL_PIPELINE.PROSPECT_TYPE = MST_PROSPECT_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS MST_DEBTOR_TYPE ON TBL_PIPELINE.DEBTOR_TYPE = MST_DEBTOR_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS MST_CHANNEL ON TBL_PIPELINE.CHANNELING = MST_CHANNEL.CHANNEL_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 15) AS MST_EXTERNAL ON TBL_PIPELINE.[EKSTERNAL] = MST_EXTERNAL.MS_STD_ID
            WHERE  IS_ASSIGNED = '0' 
                   AND (FULL_NAME LIKE @PARAM) 
                   AND UNIT_CODE IN 
                        (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID)
                        ) AS LogWithRowNumbers 
                   WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + VALUE + "%");
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new PipelineInfo(dr.GetGuid(1).ToString(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5),
                        dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10),
                        dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15),
                        dr.GetString(16)));
                }
                dr.Close();
            }
            return list;
        }

        public int GetRowNumberAssignSO(string NIP, string VALUE)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"DECLARE @PROCESS_DATE DATETIME
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 

            SELECT count(1) AS jmlData FROM( 
            SELECT [ROW_ID]
            FROM TBL_PIPELINE 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS MST_PROSPECT_TYPE ON TBL_PIPELINE.PROSPECT_TYPE = MST_PROSPECT_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS MST_DEBTOR_TYPE ON TBL_PIPELINE.DEBTOR_TYPE = MST_DEBTOR_TYPE.MS_STD_ID 
                    LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS MST_CHANNEL ON TBL_PIPELINE.CHANNELING = MST_CHANNEL.CHANNEL_ID 
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 15) AS MST_EXTERNAL ON TBL_PIPELINE.[EKSTERNAL] = MST_EXTERNAL.MS_STD_ID
            WHERE  IS_ASSIGNED = '0' 
                   AND (FULL_NAME LIKE @PARAM) 
                   AND UNIT_CODE IN 
                        (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID)
                ) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + VALUE + "%");
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int UpdateAssigned(string ROW_ID, string ASSIGNED_TO, string UPDATE_BY)
        {
            int l_result = 0;

            Database db = DBConnection.CreateDatabase();
            AppParam appParam = new AppParam();
            AppParamInfo parameter = appParam.Get();
            DateTime processTime = parameter.PROCESS_DATE;

            DbConnection connection = db.CreateConnection();
            connection.Open();
            DbTransaction dbTran = connection.BeginTransaction();

            try
            {
                DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_PIPELINE SET IS_ASSIGNED = '1', IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE, ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE ROW_ID=@ROW_ID;");

                db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);
                db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, ASSIGNED_TO.Trim());
                db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, ProcessTime(processTime));
                db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, UPDATE_BY);
                db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, ProcessTime(processTime));

                if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                {
                    cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PIPELINE_DOWNLOAD WHERE ROW_ID=@ROW_ID");
                    db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);
                    db.ExecuteNonQuery(cmd, dbTran);

                    cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_PIPELINE (NOLOCK) WHERE ROW_ID=@ROW_ID");
                    db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);

                    DataSet ds = db.ExecuteDataSet(cmd, dbTran);
                    if (ValidateDataSetEmpty(ds))
                    {
                        DataRow dr = ds.Tables[0].Rows[0];
                        Branch branch = new Branch();
                        Holiday holiday = new Holiday();
                        List<DateTime> holidayList = holiday.GetHoliday();
                        MsStd std = new MsStd();
                        List<Shuba.Model.MstStdInfo> stdInfo = std.GetList(dr["JENIS_USAHA"].ToString(), 14);

                        int guFup = Convert.ToInt32(parameter.GU_FUP);
                        string AssignedTo = dr["ASSIGNED_TO"].ToString().Trim().Length < 5 ? dr["ASSIGNED_TO"].ToString().Trim().PadRight(5, 'X') : dr["ASSIGNED_TO"].ToString().Trim();
                        string SourceCode = branch.Get(Convert.ToString(dr["UNIT_CODE"])).SourceCode;
                        DateTime dateFollowUp = AddWorkDays(processTime, guFup, holidayList);
                        string jenisUsaha = "";
                        if (stdInfo.Count > 0)
                        {
                            jenisUsaha = stdInfo[0].MstID;
                        }

                        string address = dr["ADDRESS_BUSINESS"].ToString().Trim();
                        string prospect_id = dr["UNIT_CODE"].ToString().Trim() + AssignedTo + ProcessTime(processTime).ToString("yyMMddHHmmss");
                        if (prospect_id.Trim().Length < 21) prospect_id = prospect_id.Trim().PadRight(21, 'X');
                        if (prospect_id.Trim().Length > 21) prospect_id = prospect_id.Trim().Substring(0, 21);
                        if (address.Trim().Length > 255) address = address.Substring(0, 255);

                        ProspectInfo prospect = new ProspectInfo
                        {
                            PROSPECT_ID = prospect_id.Trim().ToUpper(),
                            PROSPECT_NO = "n/a",
                            AO_CODE = dr["ASSIGNED_TO"].ToString(),
                            UNIT_CODE = dr["UNIT_CODE"].ToString(),
                            SOURCE_CODE = SourceCode,
                            JENIS_USAHA = jenisUsaha,
                            FULL_NAME = dr["FULL_NAME"].ToString(),
                            RENCANA_PLAFON = dr["RENCANA_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["RENCANA_PLAFON"].ToString()),
                            MOBILE_PHONE = dr["MOBILE_PHONE"].ToString(),
                            OFFICE_PHONE = dr["OFFICE_PHONE"].ToString(),
                            RESIDENTIAL_PHONE = dr["RESIDENTIAL_PHONE"].ToString(),
                            KODYA_KAB_BUSINESS = dr["KODYA_KAB_BUSINESS"].ToString(),
                            KECAMATAN_BUSINESS = dr["KECAMATAN_BUSINESS"].ToString(),
                            KELURAHAN_BUSINESS = dr["KELURAHAN_BUSINESS"].ToString(),
                            ADDRESS_BUSINESS = address,
                            ZIP_CODE_BUSINESS = dr["ZIP_CODE_BUSINESS"].ToString(),
                            PROGRESS_STATUS = "NEW",
                            PROSPECT_TYPE = dr["PROSPECT_TYPE"].ToString(),
                            DEBTOR_TYPE = dr["DEBTOR_TYPE"].ToString(),
                            CHANNELING = dr["CHANNELING"].ToString(),
                            ENTRY_BY = UPDATE_BY,
                            ENTRY_DATE = ProcessTime(processTime),
                            GU_FUP = dateFollowUp,
                            IS_DEDUP = "0",
                            OMZET_1 = dr["OMZET_1"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_1"].ToString()),
                            OMZET_2 = dr["OMZET_2"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_2"].ToString()),
                            OMZET_3 = dr["OMZET_3"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_3"].ToString()),
                            OMZET_4 = dr["OMZET_4"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_4"].ToString()),
                            OMZET_5 = dr["OMZET_5"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_5"].ToString()),
                            OMZET_6 = dr["OMZET_6"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_6"].ToString()),
                            OMZET_7 = dr["OMZET_7"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_7"].ToString()),
                            OMZET_8 = dr["OMZET_8"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_8"].ToString()),
                            OMZET_9 = dr["OMZET_9"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_9"].ToString()),
                            OMZET_10 = dr["OMZET_10"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_10"].ToString()),
                            OMZET_11 = dr["OMZET_11"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_11"].ToString()),
                            OMZET_12 = dr["OMZET_12"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_12"].ToString()),
                            OMZET_AVERAGE = dr["OMZET_AVERAGE"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_AVERAGE"].ToString()),
                            EKSTERNAL = dr["EKSTERNAL"].ToString(),
                            REFERENSI = dr["REFERENSI"].ToString(),
                            ALTERNATE_FOR = "",

                            FLAG_FREEZE = Int16.Parse(dr["FLAG_FREEZE"].ToString())
                        };

                        cmd = db.GetSqlStringCommand(@"INSERT INTO TBL_PROSPECT 
                                (PROSPECT_ID, PROSPECT_NO, UNIT_CODE, SOURCE_CODE, AO_CODE, 
                                 FULL_NAME, JENIS_USAHA, PROGRESS_STATUS, RENCANA_PLAFON, MOBILE_PHONE, 
                                 OFFICE_PHONE, RESIDENTIAL_PHONE, KODYA_KAB_BUSINESS, KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, 
                                 ADDRESS_BUSINESS, ZIP_CODE_BUSINESS, IS_DOCUMENT_COMPLETED, ENTRY_BY, ENTRY_DATE, 
                                 UPDATE_BY, UPDATE_DATE, PIPELINE_START_TIME, GU_FUP, IS_DEDUP,
                                 PROSPECT_TYPE, DEBTOR_TYPE, CHANNELING,
                                 OMZET_1, OMZET_2, OMZET_3, OMZET_4, OMZET_5,
                                 OMZET_6, OMZET_7, OMZET_8, OMZET_9, OMZET_10,
                                 OMZET_11, OMZET_12, OMZET_AVERAGE, IS_PIPELINE_KP, EKSTERNAL, REFERENSI, NIP, ALTERNATE_FOR, KARAKTER, FLAG_FREEZE) 
                            VALUES (@PROSPECT_ID, @PROSPECT_NO, @UNIT_CODE, @SOURCE_CODE, @AO_CODE, 
                                 @FULL_NAME, @JENIS_USAHA, @PROGRESS_STATUS, @RENCANA_PLAFON, @MOBILE_PHONE, 
                                 @OFFICE_PHONE, @RESIDENTIAL_PHONE, @KODYA_KAB_BUSINESS, @KECAMATAN_BUSINESS, @KELURAHAN_BUSINESS, 
                                 @ADDRESS_BUSINESS, @ZIP_CODE_BUSINESS, @IS_DOCUMENT_COMPLETED, @ENTRY_BY, @ENTRY_DATE, 
                                 @UPDATE_BY, @UPDATE_DATE, @PIPELINE_START_TIME, @GU_FUP, @IS_DEDUP,
                                 @PROSPECT_TYPE, @DEBTOR_TYPE, @CHANNELING,
                                 @OMZET_1, @OMZET_2, @OMZET_3, @OMZET_4, @OMZET_5,
                                 @OMZET_6, @OMZET_7, @OMZET_8, @OMZET_9, @OMZET_10,
                                 @OMZET_11, @OMZET_12, @OMZET_AVERAGE, @IS_PIPELINE_KP, @EKSTERNAL, @REFERENSI, @NIP, @ALTERNATE_FOR, @KARAKTER, @FLAG_FREEZE)");

                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID.Trim().ToUpper());
                        db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, prospect.PROSPECT_NO);
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, prospect.UNIT_CODE);
                        db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, prospect.SOURCE_CODE);
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, prospect.AO_CODE);
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, prospect.FULL_NAME);
                        db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, prospect.JENIS_USAHA);
                        db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, prospect.PROGRESS_STATUS);
                        db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, prospect.RENCANA_PLAFON);
                        db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, prospect.MOBILE_PHONE);
                        db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, prospect.OFFICE_PHONE);
                        db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, prospect.RESIDENTIAL_PHONE);
                        db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, prospect.KODYA_KAB_BUSINESS);
                        db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, prospect.KECAMATAN_BUSINESS);
                        db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, prospect.KELURAHAN_BUSINESS);
                        db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, prospect.ADDRESS_BUSINESS);
                        db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, prospect.ZIP_CODE_BUSINESS);
                        db.AddInParameter(cmd, "@IS_DOCUMENT_COMPLETED", DbType.String, "0");
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, prospect.ENTRY_BY);
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, prospect.ENTRY_BY);
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                        db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, prospect.ENTRY_DATE);
                        db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, prospect.GU_FUP);
                        db.AddInParameter(cmd, "@IS_DEDUP", DbType.String, prospect.IS_DEDUP);
                        db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, prospect.PROSPECT_TYPE);
                        db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, prospect.DEBTOR_TYPE);
                        db.AddInParameter(cmd, "@CHANNELING", DbType.String, prospect.CHANNELING);
                        db.AddInParameter(cmd, "@OMZET_1", DbType.Double, prospect.OMZET_1);
                        db.AddInParameter(cmd, "@OMZET_2", DbType.Double, prospect.OMZET_2);
                        db.AddInParameter(cmd, "@OMZET_3", DbType.Double, prospect.OMZET_3);
                        db.AddInParameter(cmd, "@OMZET_4", DbType.Double, prospect.OMZET_4);
                        db.AddInParameter(cmd, "@OMZET_5", DbType.Double, prospect.OMZET_5);
                        db.AddInParameter(cmd, "@OMZET_6", DbType.Double, prospect.OMZET_6);
                        db.AddInParameter(cmd, "@OMZET_7", DbType.Double, prospect.OMZET_7);
                        db.AddInParameter(cmd, "@OMZET_8", DbType.Double, prospect.OMZET_8);
                        db.AddInParameter(cmd, "@OMZET_9", DbType.Double, prospect.OMZET_9);
                        db.AddInParameter(cmd, "@OMZET_10", DbType.Double, prospect.OMZET_10);
                        db.AddInParameter(cmd, "@OMZET_11", DbType.Double, prospect.OMZET_11);
                        db.AddInParameter(cmd, "@OMZET_12", DbType.Double, prospect.OMZET_12);
                        db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double, prospect.OMZET_AVERAGE);
                        db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String, "1");
                        db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, prospect.EKSTERNAL);
                        db.AddInParameter(cmd, "@REFERENSI", DbType.String, prospect.REFERENSI);
                        db.AddInParameter(cmd, "@NIP", DbType.String, prospect.ENTRY_BY);
                        db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, prospect.ALTERNATE_FOR);
                        db.AddInParameter(cmd, "@KARAKTER", DbType.String, "3");

                        db.AddInParameter(cmd, "@FLAG_FREEZE", DbType.Int16, prospect.FLAG_FREEZE);
                        if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                        {
                            cmd = db.GetSqlStringCommand(@"IF NOT EXISTS (SELECT * FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID) INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");
                            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID);
                            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, prospect.ENTRY_DATE);

                            l_result = db.ExecuteNonQuery(cmd, dbTran);
                        }
                    }
                }
                if (l_result > 0) dbTran.Commit();
                else dbTran.Rollback();
            }
            catch (Exception)
            {
                dbTran.Rollback();
            }
            finally
            {
                if (connection.State == ConnectionState.Open) connection.Close();
            }
            return l_result;
        }

        private DateTime ProcessTime(DateTime ProcesingDate)
        {
            return new DateTime(ProcesingDate.Year, ProcesingDate.Month, ProcesingDate.Day, DateTime.Now.Hour,
                DateTime.Now.Minute, DateTime.Now.Second, DateTime.Now.Millisecond, ProcesingDate.Kind);
        }

        bool ValidateDataSetEmpty(DataSet vDataSet)
        {
            if (vDataSet != null)
            {
                if (vDataSet.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
            }
            return false;
        }

        private DateTime AddWorkDays(DateTime date, int workingDays, List<DateTime> Holidays)
        {
            int direction = workingDays < 0 ? -1 : 1;
            DateTime newDate = date.Date;
            while (workingDays != 0)
            {
                newDate = newDate.AddDays(direction);
                if (newDate.DayOfWeek != DayOfWeek.Saturday &&
                    newDate.DayOfWeek != DayOfWeek.Sunday &&
                    !Holidays.Contains(newDate))
                {
                    workingDays -= direction;
                }
            }
            return newDate;
        }

        public int Update(PipelineInfo PIPELINE)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PIPELINE SET IS_DOWNLOADED = '0', IS_ASSIGNED = '0' 
                                WHERE UNIT_CODE = @UNIT_CODE 
		                        AND FULL_NAME = @FULL_NAME 
		                        AND KODYA_KAB_BUSINESS = @KODYA_KAB_BUSINESS
		                        AND ADDRESS_BUSINESS = @ADDRESS_BUSINESS
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PIPELINE
                                (
                                    UNIT_CODE, FULL_NAME, JENIS_USAHA, RENCANA_PLAFON,
                                    MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE, KODYA_KAB_BUSINESS,
                                    KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, ADDRESS_BUSINESS, 
                                    ZIP_CODE_BUSINESS, DATE_VISIT_PLAN, PROSPECT_TYPE, DEBTOR_TYPE, CHANNELING,
                                    OMZET_1, OMZET_2, OMZET_3, OMZET_4, OMZET_5, OMZET_6, OMZET_7, OMZET_8, 
                                    OMZET_9, OMZET_10, OMZET_11, OMZET_12, OMZET_AVERAGE, 
                                    ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE
                                ) 
                                VALUES
                                (
                                    LEFT(@UNIT_CODE,6), LEFT(@FULL_NAME,50),
                                    LEFT(@JENIS_USAHA,100), @RENCANA_PLAFON, LEFT(@MOBILE_PHONE,18),
                                    RIGHT(@OFFICE_PHONE,12), RIGHT(@RESIDENTIAL_PHONE,12),
                                    LEFT(@KODYA_KAB_BUSINESS,50), LEFT(@KECAMATAN_BUSINESS,50), LEFT(@KELURAHAN_BUSINESS,50),
                                    LEFT(@ADDRESS_BUSINESS,255), LEFT(@ZIP_CODE_BUSINESS,6),
                                    @DATE_VISIT_PLAN, LEFT(@PROSPECT_TYPE,10), LEFT(@DEBTOR_TYPE,10), LEFT(@CHANNELING,10), 
                                    @OMZET_1, @OMZET_2, @OMZET_3, @OMZET_4, @OMZET_5, @OMZET_6, @OMZET_7, @OMZET_8, 
                                    @OMZET_9, @OMZET_10, @OMZET_11, @OMZET_12, @OMZET_AVERAGE, 
                                    @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE
                                );

                                DELETE FROM TBL_PIPELINE_DOWNLOAD WHERE ROW_ID = @ROW_ID;
                                INSERT INTO TBL_PIPELINE_DOWNLOAD (ROW_ID) VALUES (@ROW_ID);
                            ");
                db.AddInParameter(cmd, "@ROW_ID", DbType.String, PIPELINE.ROW_ID);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, PIPELINE.UNIT_CODE);
                db.AddInParameter(cmd, "@FULL_NAME", DbType.String, PIPELINE.FULL_NAME);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, PIPELINE.JENIS_USAHA);
                db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, PIPELINE.RENCANA_PLAFON);
                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, PIPELINE.MOBILE_PHONE);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, PIPELINE.OFFICE_PHONE);
                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, PIPELINE.RESIDENTIAL_PHONE);
                db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, PIPELINE.KODYA_KAB_BUSINESS);
                db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, PIPELINE.KECAMATAN_BUSINESS);
                db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, PIPELINE.KELURAHAN_BUSINESS);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, PIPELINE.ADDRESS_BUSINESS);
                db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, PIPELINE.ZIP_CODE_BUSINESS);
                db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, PIPELINE.DATE_VISIT_PLAN);
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, PIPELINE.PROSPECT_TYPE);
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, PIPELINE.DEBTOR_TYPE);
                db.AddInParameter(cmd, "@CHANNELING", DbType.String, PIPELINE.CHANNELING);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, PIPELINE.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, PIPELINE.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, PIPELINE.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, PIPELINE.UPDATE_DATE);
                db.AddInParameter(cmd, "@OMZET_1", DbType.String, PIPELINE.OMZET_1);
                db.AddInParameter(cmd, "@OMZET_2", DbType.String, PIPELINE.OMZET_2);
                db.AddInParameter(cmd, "@OMZET_3", DbType.String, PIPELINE.OMZET_3);
                db.AddInParameter(cmd, "@OMZET_4", DbType.String, PIPELINE.OMZET_4);
                db.AddInParameter(cmd, "@OMZET_5", DbType.String, PIPELINE.OMZET_5);
                db.AddInParameter(cmd, "@OMZET_6", DbType.String, PIPELINE.OMZET_6);
                db.AddInParameter(cmd, "@OMZET_7", DbType.String, PIPELINE.OMZET_7);
                db.AddInParameter(cmd, "@OMZET_8", DbType.String, PIPELINE.OMZET_8);
                db.AddInParameter(cmd, "@OMZET_9", DbType.String, PIPELINE.OMZET_9);
                db.AddInParameter(cmd, "@OMZET_10", DbType.String, PIPELINE.OMZET_10);
                db.AddInParameter(cmd, "@OMZET_11", DbType.String, PIPELINE.OMZET_11);
                db.AddInParameter(cmd, "@OMZET_12", DbType.String, PIPELINE.OMZET_12);
                db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.String, PIPELINE.OMZET_AVERAGE);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }

        // PENAMBAHAN UNTUK PERBAIKAN UPLOAD
        public PipelineBulkInfo GetAll(string ROW_ID)
        {
            PipelineBulkInfo ci = new PipelineBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT ROW_ID,UNIT_CODE,FULL_NAME,JENIS_USAHA,RENCANA_PLAFON
                                                    ,MOBILE_PHONE,OFFICE_PHONE,RESIDENTIAL_PHONE,KODYA_KAB_BUSINESS
                                                    ,KECAMATAN_BUSINESS,KELURAHAN_BUSINESS,ADDRESS_BUSINESS,ZIP_CODE_BUSINESS
                                                    ,DATE_VISIT_PLAN,PROSPECT_TYPE,DEBTOR_TYPE,CHANNELING,OMZET_1,OMZET_2
                                                    ,OMZET_3,OMZET_4,OMZET_5,OMZET_6,OMZET_7,OMZET_8,OMZET_9,OMZET_10
                                                    ,OMZET_11,OMZET_12,OMZET_AVERAGE,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE
                                                    ,IS_DOWNLOADED,DOWNLOAD_BY,DOWNLOAD_DATE,EKSTERNAL,REFERENSI,IS_ASSIGNED,ASSIGNED_TO
                                                    ,ASSIGNED_DATE,BATCH_ID 
                                                     FROM TBL_PIPELINE WHERE LTRIM(RTRIM(ROW_ID)) LIKE '' + @ROW_ID + ''");
            db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID.Trim());
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new PipelineBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDouble(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetDateTime(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetDouble(17), dr.GetDouble(18), dr.GetDouble(19), dr.GetDouble(20), dr.GetDouble(21), dr.GetDouble(22), dr.GetDouble(23), dr.GetDouble(24), dr.GetDouble(25), dr.GetDouble(26), dr.GetDouble(27), dr.GetDouble(28), dr.GetDouble(29), dr.GetString(30), dr.GetDateTime(31), dr.GetString(32), dr.GetDateTime(33), dr.GetString(34), dr.GetString(35), dr.GetDateTime(36), dr.GetString(37), dr.GetString(38), dr.GetString(39), dr.GetString(40), dr.GetDateTime(41), dr.GetString(42));
                }
                dr.Close();
            }
            return ci;
        }

        public int Savetotmp(PipelineBulkInfo PIPELINE_TMP)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PIPELINE_TMP SET 
                                    UNIT_CODE = LEFT(@UNIT_CODE,6), FULL_NAME = LEFT(@FULL_NAME,50),
                                    JENIS_USAHA = LEFT(@JENIS_USAHA,100), RENCANA_PLAFON = @RENCANA_PLAFON, 
                                    MOBILE_PHONE = LEFT(@MOBILE_PHONE,18), OFFICE_PHONE = RIGHT(@OFFICE_PHONE,12), 
                                    RESIDENTIAL_PHONE = RIGHT(@RESIDENTIAL_PHONE,12), KODYA_KAB_BUSINESS = LEFT(@KODYA_KAB_BUSINESS,50),
                                    KECAMATAN_BUSINESS = LEFT(@KECAMATAN_BUSINESS,50), KELURAHAN_BUSINESS = LEFT(@KELURAHAN_BUSINESS,50),
                                    ADDRESS_BUSINESS = LEFT(@ADDRESS_BUSINESS,255), ZIP_CODE_BUSINESS = LEFT(@ZIP_CODE_BUSINESS,6),
                                    PROSPECT_TYPE = LEFT(@PROSPECT_TYPE,10), DEBTOR_TYPE = LEFT(@DEBTOR_TYPE,10),
                                    CHANNELING = LEFT(@CHANNELING,10), OMZET_1 = @OMZET_1, OMZET_2 = @OMZET_2, OMZET_3 = @OMZET_3,
                                    OMZET_4 = @OMZET_4, OMZET_5 = @OMZET_5, OMZET_6 = @OMZET_6, OMZET_7 = @OMZET_7, OMZET_8 = @OMZET_8,
                                    OMZET_9 = @OMZET_9, OMZET_10 = @OMZET_10, OMZET_11 = @OMZET_11, OMZET_12 = @OMZET_12, 
                                    OMZET_AVERAGE = @OMZET_AVERAGE, ENTRY_BY = @ENTRY_BY, 
                                    ENTRY_DATE = @ENTRY_DATE, UPDATE_BY = @UPDATE_BY,
                                    UPDATE_DATE = @UPDATE_DATE, ACT_TYPE = @ACT_TYPE, [EKSTERNAL] = @EKSTERNAL, REFERENSI = @REFERENSI, BATCH_ID = @BATCH_ID
                                WHERE (LTRIM(RTRIM(UNIT_CODE)) = LTRIM(RTRIM(@UNIT_CODE))) AND (LTRIM(RTRIM(FULL_NAME))=LTRIM(RTRIM(@FULL_NAME)))
                                AND (LTRIM(RTRIM(KODYA_KAB_BUSINESS)) = LTRIM(RTRIM(@KODYA_KAB_BUSINESS))) AND (LTRIM(RTRIM(ADDRESS_BUSINESS)) = LTRIM(RTRIM(@ADDRESS_BUSINESS)));
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PIPELINE_TMP
                                (
                                    UNIT_CODE, FULL_NAME, JENIS_USAHA, RENCANA_PLAFON,
                                    MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE, KODYA_KAB_BUSINESS,
                                    KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, ADDRESS_BUSINESS, 
                                    ZIP_CODE_BUSINESS, DATE_VISIT_PLAN, PROSPECT_TYPE, DEBTOR_TYPE, CHANNELING,
                                    OMZET_1, OMZET_2, OMZET_3, OMZET_4, OMZET_5, OMZET_6, OMZET_7, OMZET_8, 
                                    OMZET_9, OMZET_10, OMZET_11, OMZET_12, OMZET_AVERAGE, 
                                    ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE, [EKSTERNAL], REFERENSI, BATCH_ID
                                ) 
                                VALUES
                                (
                                    LEFT(@UNIT_CODE,6), LEFT(@FULL_NAME,50),
                                    LEFT(@JENIS_USAHA,100), @RENCANA_PLAFON, LEFT(@MOBILE_PHONE,18),
                                    RIGHT(@OFFICE_PHONE,12), RIGHT(@RESIDENTIAL_PHONE,12),
                                    LEFT(@KODYA_KAB_BUSINESS,50), LEFT(@KECAMATAN_BUSINESS,50), LEFT(@KELURAHAN_BUSINESS,50),
                                    LEFT(@ADDRESS_BUSINESS,255), LEFT(@ZIP_CODE_BUSINESS,6),
                                    @DATE_VISIT_PLAN, LEFT(@PROSPECT_TYPE,10), LEFT(@DEBTOR_TYPE,10), LEFT(@CHANNELING,10), 
                                    @OMZET_1, @OMZET_2, @OMZET_3, @OMZET_4, @OMZET_5, @OMZET_6, @OMZET_7, @OMZET_8, 
                                    @OMZET_9, @OMZET_10, @OMZET_11, @OMZET_12, @OMZET_AVERAGE, 
                                    @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE, @EKSTERNAL, @REFERENSI, @BATCH_ID
                                );
                            ");
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, PIPELINE_TMP.UNIT_CODE);
                db.AddInParameter(cmd, "@FULL_NAME", DbType.String, PIPELINE_TMP.FULL_NAME);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, PIPELINE_TMP.JENIS_USAHA);
                db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, PIPELINE_TMP.RENCANA_PLAFON);
                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, PIPELINE_TMP.MOBILE_PHONE);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, PIPELINE_TMP.OFFICE_PHONE);
                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, PIPELINE_TMP.RESIDENTIAL_PHONE);
                db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, PIPELINE_TMP.KODYA_KAB_BUSINESS);
                db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, PIPELINE_TMP.KECAMATAN_BUSINESS);
                db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, PIPELINE_TMP.KELURAHAN_BUSINESS);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, PIPELINE_TMP.ADDRESS_BUSINESS);
                db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, PIPELINE_TMP.ZIP_CODE_BUSINESS);
                db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, PIPELINE_TMP.DATE_VISIT_PLAN);
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, PIPELINE_TMP.PROSPECT_TYPE);
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, PIPELINE_TMP.DEBTOR_TYPE);
                db.AddInParameter(cmd, "@CHANNELING", DbType.String, PIPELINE_TMP.CHANNELING);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, PIPELINE_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, PIPELINE_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, PIPELINE_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, PIPELINE_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, PIPELINE_TMP.ACT_TYPE);
                db.AddInParameter(cmd, "@OMZET_1", DbType.String, PIPELINE_TMP.OMZET_1);
                db.AddInParameter(cmd, "@OMZET_2", DbType.String, PIPELINE_TMP.OMZET_2);
                db.AddInParameter(cmd, "@OMZET_3", DbType.String, PIPELINE_TMP.OMZET_3);
                db.AddInParameter(cmd, "@OMZET_4", DbType.String, PIPELINE_TMP.OMZET_4);
                db.AddInParameter(cmd, "@OMZET_5", DbType.String, PIPELINE_TMP.OMZET_5);
                db.AddInParameter(cmd, "@OMZET_6", DbType.String, PIPELINE_TMP.OMZET_6);
                db.AddInParameter(cmd, "@OMZET_7", DbType.String, PIPELINE_TMP.OMZET_7);
                db.AddInParameter(cmd, "@OMZET_8", DbType.String, PIPELINE_TMP.OMZET_8);
                db.AddInParameter(cmd, "@OMZET_9", DbType.String, PIPELINE_TMP.OMZET_9);
                db.AddInParameter(cmd, "@OMZET_10", DbType.String, PIPELINE_TMP.OMZET_10);
                db.AddInParameter(cmd, "@OMZET_11", DbType.String, PIPELINE_TMP.OMZET_11);
                db.AddInParameter(cmd, "@OMZET_12", DbType.String, PIPELINE_TMP.OMZET_12);
                db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.String, PIPELINE_TMP.OMZET_AVERAGE);
                db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, PIPELINE_TMP.EKSTERNAL);
                db.AddInParameter(cmd, "@REFERENSI", DbType.String, PIPELINE_TMP.REFERENSI);
                db.AddInParameter(cmd, "@BATCH_ID", DbType.String, PIPELINE_TMP.BATCH_ID);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }
    }
}
